﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using Runda.CMS.Order.Entity;
using Runda.CMS.Common.Utility;
using System.Linq.Expressions;
using System.Data;
using System.Data.Common;
using System.Data.Sql;
using System.Data.SqlClient;

namespace Runda.CMS.Order.Repository
{
    public class OrderRepository
    {
        private string db = ConfigurationManager.ConnectionStrings["OrderConnectionString"].ConnectionString;
                     
        public Object findOrderListByCustomer(String customerName,string inventoryName,string billNum,string sendNum,String orderNum,int pageSize, int pageIndex, out int recordCount){
            String sql = "select * from (select b.cCusCode,b.cCusName,isnull(cast(a.iQuantity as decimal(18,2)),0.00) as iQuantity,a.cOrderId,c.cInvName,isnull(cast(d.sendIQuantity as decimal(18,2)),0.00) as sendQuantity,c.cInvBarCode,c.cInvCode,isnull(cast(d.sendIsQuantity as decimal(18,2)),0.00) as sendIsQuantity,c.cInvStd as inventoryStandard,a.cDate as orderDate, a.cNewTime as orderTime,a.cOrderId as orderDetailID from Out_Orders a inner join Out_Customer b on a.cCusCode = b.cCusCode inner join Out_Inventory c on c.cInvCode= a.cInvCode " + (billNum == "" ? " left " : " inner ") + " join  (select cCusCode,cOrderId,cInvBarCode,SUM(iQuantity) as sendIQuantity,SUM(iSQuantity) as sendIsQuantity from Out_RdRecord " + (billNum == "" ? "" : " where cVouchId='" + billNum + "'") + " group by cCusCode,cOrderId,cInvBarCode ) d on a.cOrderId = d.cOrderId and a.cInvCode =(select cInvCode from Out_Inventory where cInvBarCode = d.cInvBarCode) and a.cCusCode = d.cCusCode  " + (sendNum == "" ? " left " : " inner ") + " join  (select cOrderId,cCusCode,cInvCode,SUM(quantity) as receiveQuantity from VouchSend " + (sendNum == "" ? "" : "where cSendbillNo='" + sendNum + "'") + " group by cOrderId,cCusCode,cInvCode)  e on e.cInvCode = c.cInvBarCode  and e.cCusCode = a.cCusCode and e.cOrderId = a.cOrderId) as a where 1=1 ";
            if(customerName !=""){
                sql+=" and a.cCusName like'%"+customerName+"%'";
            }
            if(inventoryName != ""){
                sql +=" and a.cInvName like '%"+inventoryName+"%'";
            }
            if (orderNum != "") {
                sql += " and cOrderId='" + orderNum + "'";
            }
            OrderDataContext context = new OrderDataContext(db);    
            context.Connection.Open();
            SqlCommand command = new SqlCommand(sql, (SqlConnection)context.Connection);
            List<OrderInfo> list = new List<OrderInfo>();
            using (DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection)) {              
                list =  context.Translate<OrderInfo>(reader).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();              
            }
            context.Connection.Open();          
            using (DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
            {
                recordCount = context.Translate<OrderInfo>(reader).Count();
            }
            return list;
        }

        public Object findOrderinfo(String customerName, string inventoryName, string billNum, string sendNum, String orderNum, int pageSize, int pageIndex, out int recordCount)
        {
            String sql = @"select * from (select b.cCusCode,b.cCusName,isnull(cast(a.iQuantity as decimal(18,2)),0.00) as iQuantity,a.cOrderId,a.cInvName
                ,a.cInvCode,a.cInvStd as inventoryStandard,a.cDate as orderDate, a.cNewTime as orderTime,a.cOrderId as orderDetailID 
                from Out_Orders a 
                inner join Out_Customer b on a.cCusCode = b.cCusCode) as a where 1=1 ";
            if (customerName != "")
            {
                sql += " and a.cCusName like'%" + customerName + "%'";
            }
            if (inventoryName != "")
            {
                sql += " and a.cInvName like '%" + inventoryName + "%'";
            }
            if (orderNum != "")
            {
                sql += " and cOrderId='" + orderNum + "'";
            }
            OrderDataContext context = new OrderDataContext(db);
            context.Connection.Open();
            SqlCommand command = new SqlCommand(sql, (SqlConnection)context.Connection);
            List<OrderInfo> list = new List<OrderInfo>();
            using (DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
            {
                list = context.Translate<OrderInfo>(reader).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
            context.Connection.Open();
            using (DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
            {
                recordCount = context.Translate<OrderInfo>(reader).Count();
            }
            return list;
        }

        public List<OrderInfo> CustomerQuery(String customerCode, string inventoryName, DateTime? shippingDateFrom, DateTime? shippingDateTo, int pageSize, int pageIndex, out int recordCount) {
            String sql = @"select * from (select a.cCusCode,0.00 as sendIsQuantity, a.cVouchId,
convert(char(10),a.cDate,121) as sendDate,a.cCustomerName as cCusName,a.cOrderId, isnull(cast(g.iQuantity as decimal(18,2)),0.00) as iQuantity,
isnull(cast(a.sendQuantity as decimal(18,4)),0.00) as sendQuantity,
a.cOrderId as orderDetailID,g.cDate as orderDate, g.cNewTime as orderTime,case when ReceivedDate is not null then '已签收' else case when cSendcompany is not null then '快递中' else  a.cStatus end end as cStatus ,a.cDate, 
cSendbillNo,ReceivedMan,
 cSendcompany,convert(char(10),ReceivedDate,121) as ReceivedDate,convert(char(10),preReceivedDate,121) as preReceivedDate
from ({0}) as a join (select cOrderId,cDate,cNewTime,sum(iQuantity) as iQuantity from Out_Orders group by cOrderId,cDate,cNewTime) as g on g.cOrderId = a.cOrderId
   left  join  (select distinct cOrderId,cVouchId,cSendbillNo,ReceivedMan,
 cSendcompany,ReceivedDate,preReceivedDate 
 from VouchSend)  e on e.cVouchId = a.cVouchId) as a where 1=1";
            String sqlShippingRequest = "select cVouchId,cDate,cCusCode,cOrderId,cCustomerName,cStatus,sum(iQuantity) as sendQuantity from Out_RdRecord a where 1=1";
            if (inventoryName != "") {
                sqlShippingRequest += " and  cInvName like'%" + inventoryName + "%'";
            }
            if (shippingDateFrom != null)
            {
                sqlShippingRequest += " and cast(cDate as datetime) >= '" + shippingDateFrom.ToString() + "'";
            }
            if (shippingDateTo != null)
            {
                sqlShippingRequest += " and cast(cDate as datetime) < '" + shippingDateTo.ToString() + "'";
            }
            sqlShippingRequest += " group by cVouchId,cDate,cCusCode,cOrderId,cCustomerName,cStatus";
            sql = String.Format(sql, sqlShippingRequest);
            if (customerCode != "")
            {
                sql += " and cCusCode='" + customerCode + "'";
            }
            sql += " order by sendDate desc";
            OrderDataContext context = new OrderDataContext(db);
            context.Connection.Open();
            SqlCommand command = new SqlCommand(sql, (SqlConnection)context.Connection);
            List<OrderInfo> list = new List<OrderInfo>();
            using (DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
            {
                list = context.Translate<OrderInfo>(reader).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
            context.Connection.Open();
            using (DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
            {
                recordCount = context.Translate<OrderInfo>(reader).Count();
            }
            return list;
        }

        public List<CustomerOrder> findCustomerOrderDetailByOrderId(String orderId)
        {
            using (OrderDataContext context = new OrderDataContext(db))
            {
                var od = from customerOrder in context.customerOrder
                         where customerOrder.orderId == orderId
                         select customerOrder;

                return od.ToList<CustomerOrder>();
            }
        }
    }
}
